package com.code.lightning.code.oracle;

import com.code.lightning.code.base.BasicDBConnect;
import com.code.lightning.common.api.BasicDataBaseInfoAnalyse;
import com.code.lightning.common.domain.BasicDataBaseMetaData;
import com.code.lightning.common.domain.BasicTableFieldMetaData;
import com.code.lightning.common.domain.BasicTableMetaData;
import com.code.lightning.common.domain.FieldReference;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * MySQL数据库基础元数据获取
 * @author LGX_TvT
 * @date 2021-01-15 16:30
 */
@Slf4j
public class OracleInfoAnalyse implements BasicDataBaseInfoAnalyse {

    private Connection connection;

    public OracleInfoAnalyse(Connection connection) {
        this.connection = connection;
    }

    @Override
    public BasicDataBaseMetaData getDataBaseMetaData(String dbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        String sql = "SELECT d.Name SCHEMA_NAME,n.VALUE DEFAULT_CHARACTER_SET_NAME FROM v$database d, nls_database_parameters n where n.parameter='NLS_CHARACTERSET'";
        BasicDataBaseMetaData basicDataBaseMetaData = new BasicDataBaseMetaData();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                basicDataBaseMetaData.setDbCategory(connection.getMetaData().getDatabaseProductName().toUpperCase());
                basicDataBaseMetaData.setSchemaName(resultSet.getString("SCHEMA_NAME"));
                basicDataBaseMetaData.setDefaultCharacterSetName(resultSet.getString("DEFAULT_CHARACTER_SET_NAME"));
            }
            log.info("GET DataBaseMetaData SUCCESS: {}", basicDataBaseMetaData);
            return basicDataBaseMetaData;
        } catch (SQLException throwables) {
            log.error("GetDataBaseMetaData Error: dbName={}", throwables.getMessage());
            throwables.printStackTrace();
        } finally {
            BasicDBConnect.close(null, pstmt, resultSet);
        }
        return null;
    }

    @Override
    public BasicDataBaseMetaData getDataBaseMetaData(String dbName) {
        return this.getDataBaseMetaData(dbName, this.connection);
    }

    @Override
    public List<BasicDataBaseMetaData> getDataBaseMetaData(Connection connection) {
        BasicDataBaseMetaData dataBaseMetaData = this.getDataBaseMetaData("", connection);
        List<BasicDataBaseMetaData> list = new ArrayList<BasicDataBaseMetaData>();
        list.add(dataBaseMetaData);
        return list;
    }

    @Override
    public List<BasicDataBaseMetaData> getDataBaseMetaData() {
        return this.getDataBaseMetaData(this.connection);
    }

    @Override
    public List<BasicTableMetaData> getTableMetaData(String dbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        List<BasicTableMetaData> basicTableMetaDataList = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            String sql = " SELECT d.NAME,a.TABLE_NAME,a.TABLE_TYPE,v.BANNER VERSION,a.COMMENTS TABLE_COMMENT FROM v$database d,(select * from v$version where rownum=1) v ,USER_TAB_COMMENTS a ";
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                BasicTableMetaData basicTableMetaData = new BasicTableMetaData();
                basicTableMetaData.setTableSchema(resultSet.getString("NAME"));//数据表所属的数据库名
                basicTableMetaData.setTableName(resultSet.getString("TABLE_NAME"));//表名称
                basicTableMetaData.setTableType(resultSet.getString("TABLE_TYPE"));//表类型
                basicTableMetaData.setVersion(resultSet.getString("VERSION"));//数据库版本
//                basicTableMetaData.setCreateTime(resultSet.getString("CREATE_TIME"));//创建时间
//                basicTableMetaData.setUpdateTime(resultSet.getString("UPDATE_TIME"));//更新时间
                basicTableMetaData.setTableComment(resultSet.getString("TABLE_COMMENT"));//表的注释、备注
                basicTableMetaDataList.add(basicTableMetaData);
            }
            return basicTableMetaDataList;
        } catch (Exception e) {
            log.error("GetTableMetaData Error: {}", e.getMessage());
            e.printStackTrace();
        } finally {
            BasicDBConnect.close(null, pstmt, resultSet);
        }
        return null;
    }

    @Override
    public List<BasicTableMetaData> getTableMetaData(String dbName) {
        return this.getTableMetaData(dbName, this.connection);
    }

    @Override
    public BasicTableMetaData getTableMetaData(String dbName, String tbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        BasicTableMetaData basicTableMetaData = new BasicTableMetaData();
        try {
            String sql = String.format(" SELECT a.TABLE_NAME,a.TABLE_TYPE,v.BANNER VERSION,b.created CREATE_TIME,b.last_ddl_time UPDATE_TIME,a.COMMENTS TABLE_COMMENT FROM (select * from v$version where rownum=1) v ,USER_TAB_COMMENTS a " +
                    " left join dba_objects b on b.object_name = a.TABLE_NAME " +
                    " where b.object_name=a.TABLE_NAME and object_type='TABLE' and a.TABLE_NAME='%s'", tbName);
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            String url = connection.getMetaData().getURL();
            int i = url.lastIndexOf(":")+1;
            String schema = i!=0?url.substring(i):"";
            while (resultSet.next()) {
                basicTableMetaData.setTableSchema(schema);//数据表所属的数据库名
                basicTableMetaData.setTableName(resultSet.getString("TABLE_NAME"));//表名称
                basicTableMetaData.setTableType(resultSet.getString("TABLE_TYPE"));//表类型
                basicTableMetaData.setVersion(resultSet.getString("VERSION"));//数据库版本
                basicTableMetaData.setCreateTime(resultSet.getString("CREATE_TIME"));//创建时间
                basicTableMetaData.setUpdateTime(resultSet.getString("UPDATE_TIME"));//更新时间
                basicTableMetaData.setTableComment(resultSet.getString("TABLE_COMMENT"));//表的注释、备注
            }
            return basicTableMetaData;
        } catch (Exception e) {
            log.error("GetTableMetaData Error: {}", e.getMessage());
            e.printStackTrace();
        } finally {
            BasicDBConnect.close(null, pstmt, resultSet);
        }
        return null;
    }

    @Override
    public BasicTableMetaData getTableMetaData(String dbName, String tbName) {
        return this.getTableMetaData(dbName, tbName, this.connection);
    }

    @Override
    public List<BasicTableFieldMetaData> getTableFieldMetaData(String dbName, String tbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        List<BasicTableFieldMetaData> dataBaseColunmsList = new ArrayList<>();
        String sql = String.format(" select " +
                " cc.column_name PRI,utc.table_name,utc.column_name,utc.data_type,utc.data_length,utc.data_precision, " +
                " utc.data_Scale,utc.nullable,utc.data_default,ucc.comments " +
                " from user_tab_columns utc " +
                " left join " +
                " user_col_comments ucc on  utc.column_name = ucc.column_name and utc.table_name=ucc.table_name " +
                " left join " +
                " (select col.table_name,col.column_name from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='P'）cc  " +
                " on cc.table_name=utc.table_name and cc.column_name=utc.column_name where utc.table_name = '%s' ", tbName);
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            String url = connection.getMetaData().getURL();
            int i = url.lastIndexOf(":")+1;
            String schema =  i!=0?url.substring(i):"";
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                BasicTableFieldMetaData dataBaseColunms = new BasicTableFieldMetaData();
                dataBaseColunms.setTableSchema(schema);
                dataBaseColunms.setTableName(resultSet.getString("TABLE_NAME"));
                dataBaseColunms.setColumnName(resultSet.getString("COLUMN_NAME"));
                dataBaseColunms.setOrdinalPosition(resultSet.getString("DATA_PRECISION"));
                dataBaseColunms.setColumnDefault(resultSet.getString("DATA_DEFAULT"));
                dataBaseColunms.setIsNullable(resultSet.getString("NULLABLE"));
                dataBaseColunms.setDataType(resultSet.getString("DATA_TYPE"));
                dataBaseColunms.setCharacterOctetLength(resultSet.getString("DATA_LENGTH"));
                dataBaseColunms.setColumnKey(resultSet.getString("PRI")==resultSet.getString("COLUMN_NAME")?"PRI":null);
                dataBaseColunms.setColumnComment(resultSet.getString("COMMENTS"));
                dataBaseColunms.setOrdinalPosition("999");

                // 获取主外键
                String fkSql = String.format("select t1.table_name TABLE_NAME, " +
                        " t2.table_name REFERENCED_TABLE_NAME, " +
                        " a1.column_name COLUMN_NAME, " +
                        " a2.column_name REFERENCED_COLUMN_NAME " +
                        " from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2 " +
                        " where " +
                        " t1.table_name = '%s' and a1.column_name='%s' and " +
                        " t1.r_constraint_name = t2.constraint_name and " +
                        " t1.constraint_name = a1.constraint_name and " +
                        " t1.r_constraint_name = a2.constraint_name and a1.position=a2.position ", dataBaseColunms.getTableName(), dataBaseColunms.getColumnName());
                PreparedStatement fkpstmt = connection.prepareStatement(fkSql);
                ResultSet fkResultSet = fkpstmt.executeQuery();
                while (fkResultSet.next()) {
                    FieldReference fieldReference = new FieldReference();
                    fieldReference.setTableSchema(connection.getMetaData().getSchemaTerm());
                    fieldReference.setTableName(fkResultSet.getString("TABLE_NAME"));
                    fieldReference.setColumnName(fkResultSet.getString("COLUMN_NAME"));
                    fieldReference.setReferencesTableSchema(connection.getSchema());
                    fieldReference.setReferenceTableName(fkResultSet.getString("REFERENCED_TABLE_NAME"));
                    fieldReference.setReferenceColumnName(fkResultSet.getString("REFERENCED_COLUMN_NAME"));
                    dataBaseColunms.setReference(fieldReference);
                }
                dataBaseColunmsList.add(dataBaseColunms);
                BasicDBConnect.close(null, fkpstmt, fkResultSet);
            }
            return dataBaseColunmsList;
        } catch (SQLException throwables) {
            log.error("GetTableFieldMetaData Error: {}", throwables.getMessage());
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<BasicTableFieldMetaData> getTableFieldMetaData(String dbName, String tbName) {
        return this.getTableFieldMetaData(dbName, tbName, this.connection);
    }

    /**
     * 关闭数据库连接
     * @param connection 数据库连接
     */
    public void close(Connection connection) {
         BasicDBConnect.close(this.connection, null, null);
    }

    /**
     * 关闭数据库连接
     */
    public void close() {
        this.close(this.connection);
    }

}
